package com.genghis.ptas.friend.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.ptas.constant.DictConstant;
import com.genghis.ptas.friend.dao.FriendDao;
import com.genghis.ptas.friend.entity.Friend;
import com.genghis.ptas.friend.entity.FriendChatRecord;
import com.genghis.ptas.friend.entity.FriendGroup;
import com.genghis.ptas.security.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: gaoyang
 * To change this template use File | Settings | File Templates.
 */
@Repository("friendDao")
public class FriendDaoImpl extends PageDao implements FriendDao {

    @Override
    public void addFriend(Friend friend) {
        String sql = "INSERT INTO friends(`user_id`,`friend_id`,state, inbox_id) VALUES(:userId, :friendId, :state, :inboxId)";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friend));
    }

    @Override
    public void delFriend(String ids) {
        String sql = "DELETE FROM friends WHERE friend_id in(" + ids + ")";
        getJdbcTemplate().update(sql);
    }

    @Override
    public Friend findFriendById(int userId) {
        String sql = "select * from friends where friend_id=" + userId;
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<Friend>(Friend.class));
    }

    @Override
    public void addFriendInfo(Friend friend) {
        String sql = "INSERT INTO friend_info(user_id, praise_number, on_line) VALUES(:userId, " + DictConstant.VOID_VALUE + ", " + DictConstant.VOID_VALUE + ")";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friend));
    }

    @Override
    public void editFriendInfoByUserId(Friend friend) {
        String prise = -1 == friend.getPraiseNumber() ? "" : "praise_number = :praiseNumber,";
        String sql = "UPDATE friend_info SET " + prise + " on_line = :onLine WHERE user_id = :userId ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friend));
    }

    @Override
    public List<User> findFriends(Friend friend) {
        StringBuilder sql = new StringBuilder("SELECT *, friend_id checked,state password FROM (\n" +
                "SELECT u.user_name, b.`sex`, b.`address`, b.`phone`, b.`email`, b.`career`,\n" +
                " b.`education`, b.`photo_url`, b.`experience`, b.`country`, u.id  FROM `sys_user` u, `user_base_info` b, `sys_user_role` r \n" +
                "WHERE u.`id`=b.`user_id` AND r.`user_id`=u.`id` AND r.`role_id`=2 ");
        if (!"".equals(friend.getFriendName()) && null != friend.getFriendName()) {
            sql.append(" AND u.user_name LIKE '%").append(friend.getFriendName()).append("%'");
        }
        sql.append(") al LEFT JOIN (\n" +
                "SELECT f.friend_id,state FROM (\n" +
                "SELECT `user_id` friend_id, `friend_id` user_id,state FROM friends f WHERE f.`state` IN(1,0)  \n" +
                "UNION  SELECT `friend_id` ,`user_id`,state FROM friends f WHERE f.`state` IN(1,0) \n" +
                ") f WHERE f.user_id=").append(friend.getUserId()).append(" ) fr ON al.id=fr.friend_id");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<User>(User.class));
    }

    @Override
    public void changeApplyState(Friend friend) {
        String sql = "UPDATE friends SET state = :state WHERE user_id = :userId AND friend_id = :friendId ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friend));
    }

    @Override
    public List<Friend> listAllFriend(Friend friend) {
        StringBuilder sql = new StringBuilder("SELECT u.user_name friend_name,u.id friend_id, fi.*,b.`photo_url` friend_photo_url FROM (\n" +
                "SELECT `user_id` friend_id, `friend_id` user_id FROM friends f WHERE f.`state`=1  \n" +
                "UNION  SELECT `friend_id` ,`user_id` FROM friends f WHERE f.`state`=1\n" +
                ") f, friend_info fi,sys_user u,`user_base_info` b WHERE fi.user_id=u.id AND f.user_id=" + friend.getUserId() + " AND f.friend_id=fi.user_id AND u.`id`=b.`user_id`\n ");
        if (!"".equals(friend.getFriendName()) && null != friend.getFriendName()) {
            sql.append(" AND u.user_name LIKE '%").append(friend.getFriendName()).append("%'");
        }
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<Friend>(Friend.class));
    }

    @Override
    public int addFriendGroup(FriendGroup friendGroup) {
        String sql = "INSERT INTO friend_group(category) VALUES(:category)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friendGroup), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public void addFriendGroupUser(FriendGroup friendGroup) {
        String sql = "INSERT INTO friend_group_user(`friend_group_id`, `user_id`, `state`) VALUES(:id, :userId, :state)";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friendGroup));
    }

    @Override
    public void addChatRecord(FriendChatRecord friendChatRecord) {
        String sql = "INSERT INTO friend_chats_record(`content`,`user_id`,`send_date`,`friend_group_id`) VALUES(:content, :userId, :sendDate, :friendGroupId)";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(friendChatRecord));
    }

    @Override
    public int findFriendGroupId(Friend friend) {
        String sql = "SELECT friend_group_id id FROM `friend_group_user` WHERE `friend_group_id` IN(\n" +
                "SELECT fg.`id` FROM `friend_group` fg,`friend_group_user` fgu WHERE fg.`id`=fgu.`friend_group_id` AND fg.`category`=1 AND state=1 AND fgu.`user_id`=" + friend.getUserId() + "\n" +
                ") AND `user_id`=" + friend.getFriendId() + " AND state=1";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<FriendGroup>(FriendGroup.class)).getId();
    }

    @Override
    public List<FriendChatRecord> findChatRecord(FriendChatRecord friendChatRecord) {
        String sql = "SELECT * FROM friend_chats_record WHERE `friend_group_id`=" + friendChatRecord.getFriendGroupId() + " AND `send_date` > '" + friendChatRecord.getSendDate() + "' order by send_date";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<FriendChatRecord>(FriendChatRecord.class));
    }

    @Override
    public void delFriendInfoByUserId(String ids) {
        String sql = "DELETE FROM friend_info WHERE user_id in(" + ids + ")";
        getJdbcTemplate().update(sql);
    }
}
